-- =======================================================
-- MAKE TABLES AND SPROCS FOR IBUYSPY StoreNuke DB
-- =======================================================

-- point to proper DB 
-- use [StoreNuke]
-- GO

CREATE TABLE [dbo].[StoreNuke_Version] (
	[Version] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (50) NULL ,
	[CategoryDescription] [nvarchar] (500) NULL ,
	[Archived] bit NOT NULL DEFAULT(0) 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_Customers] (
	[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [nvarchar] (50) NOT NULL ,
	[LastName] [nvarchar] (50) NOT NULL ,
	[EmailAddress] [nvarchar] (50) NOT NULL ,
	[Password] [nvarchar] (50) NOT NULL ,
	[IsAdmin] [bit] NOT NULL DEFAULT(0)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_OrderDetails] (
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[Quantity] [int] NOT NULL ,
	[UnitCost] [money] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_Orders] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[OrderDate] [datetime] NOT NULL ,
	[ShipDate] [datetime] NOT NULL ,
	[AddressID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_Products] (
	[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryID] [int] NOT NULL ,
	[ModelNumber] [nvarchar] (50) NULL ,
	[ModelName] [nvarchar] (50) NULL ,
	[ProductImage] [nvarchar] (50) NULL ,
	[UnitCost] [money] NOT NULL ,
	[Description] [text] NULL  ,
	[Archived] bit NOT NULL DEFAULT(0) 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_Reviews] (
	[ReviewID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[CustomerName] [nvarchar] (50) NULL ,
	[CustomerEmail] [nvarchar] (50) NULL ,
	[Rating] [int] NOT NULL ,
	[Comments] [nvarchar] (3850) NULL ,
	[Authorized] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_ShoppingCart] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[CartID] [nvarchar] (50) NULL ,
	[Quantity] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[DateCreated] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_Addresses] (
	[AddressID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[ATTN] [nvarchar] (50) NOT NULL ,
	[Address1] [nvarchar] (50) NOT NULL ,
	[Address2] [nvarchar] (50) NOT NULL ,
	[City] [nvarchar] (20) NOT NULL ,
	[RegionCode] [char] (2) NOT NULL ,
	[CountryCode] [char] (2) NOT NULL ,
	[PostalCode] [nvarchar] (10) NOT NULL ,
	[Phone1] [nvarchar] (20) NOT NULL ,
	[Phone2] [nvarchar] (20) NOT NULL ,
	[PrimaryAddress] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_CodeCountry] (
	[Code] [char] (2) NOT NULL ,
	[Description] [varchar] (100) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StoreNuke_CodeRegion] (
	[Code] [char] (2) NOT NULL ,
	[Description] [varchar] (100) NOT NULL ,
	[Country] [char] (2) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StoreNuke_Addresses] WITH NOCHECK ADD 
	CONSTRAINT [PK_StoreNuke_Addresses] PRIMARY KEY  CLUSTERED 
	(
		[AddressID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_CodeCountry] WITH NOCHECK ADD 
	CONSTRAINT [PK_CodeCountry] PRIMARY KEY  CLUSTERED 
	(
		[Code]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_CodeRegion] WITH NOCHECK ADD 
	CONSTRAINT [PK_StoreNuke_CodeRegion] PRIMARY KEY  CLUSTERED 
	(
		[Code],
		[Country]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_CodeRegion] ADD 
	CONSTRAINT [FK_StoreNuke_CodeRegion_StoreNuke_CodeCountry] FOREIGN KEY 
	(
		[Country]
	) REFERENCES [dbo].[StoreNuke_CodeCountry] (
		[Code]
	)
GO

ALTER TABLE [dbo].[StoreNuke_Categories] ADD 
	CONSTRAINT [PK_StoreNuke_Categories] PRIMARY KEY  NONCLUSTERED 
	(
		[CategoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_Customers] ADD 
	CONSTRAINT [PK_StoreNuke_Customers] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Customers] UNIQUE  NONCLUSTERED 
	(
		[EmailAddress]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_OrderDetails] ADD 
	CONSTRAINT [PK_StoreNuke_OrderDetails] PRIMARY KEY  NONCLUSTERED 
	(
		[OrderID],
		[ProductID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_Orders] ADD 
	CONSTRAINT [DF_Orders_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
	CONSTRAINT [PK_StoreNuke_Orders] PRIMARY KEY  NONCLUSTERED 
	(
		[OrderID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_Products] ADD 
	CONSTRAINT [PK_StoreNuke_Products] PRIMARY KEY  NONCLUSTERED 
	(
		[ProductID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[StoreNuke_ShoppingCart] ADD 
	CONSTRAINT [DF_ShoppingCart_Quantity] DEFAULT (1) FOR [Quantity],
	CONSTRAINT [DF_ShoppingCart_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [PK_StoreNuke_ShoppingCart] PRIMARY KEY  NONCLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_ShoppingCart] ON [dbo].[StoreNuke_ShoppingCart]([CartID], [ProductID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StoreNuke_OrderDetails] ADD 
	CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [dbo].[StoreNuke_Orders] (
		[OrderID]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[StoreNuke_Orders] ADD 
	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[StoreNuke_Customers] (
		[CustomerID]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[StoreNuke_Products] ADD 
	CONSTRAINT [FK_Products_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [dbo].[StoreNuke_Categories] (
		[CategoryID]
	)
GO

ALTER TABLE [dbo].[StoreNuke_Reviews] ADD 
	CONSTRAINT [FK_Reviews_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[StoreNuke_Products] (
		[ProductID]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[StoreNuke_ShoppingCart] ADD 
	CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[StoreNuke_Products] (
		[ProductID]
	)
GO

ALTER TABLE [dbo].[StoreNuke_Addresses] ADD 
	CONSTRAINT [FK_StoreNuke_Addresses_StoreNuke_CodeCountry] FOREIGN KEY 
	(
		[CountryCode]
	) REFERENCES [dbo].[StoreNuke_CodeCountry] (
		[Code]
	),
	CONSTRAINT [FK_StoreNuke_Addresses_StoreNuke_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[StoreNuke_Customers] (
		[CustomerID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure StoreNuke_GetVersion
AS

SELECT
	TOP 1 Version
FROM
	StoreNuke_Version
ORDER BY 
	Version DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure StoreNuke_AddVersion
(
	@Version int
)
AS

INSERT INTO StoreNuke_Version
(
    Version
)

VALUES
(
    @Version
)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE Procedure StoreNuke_CustomerAdd
(
    @FirstName   nvarchar(50),
    @LastName   nvarchar(50),
    @Email      nvarchar(50),
    @Password   nvarchar(50),
    @IsAdmin   bit,
    @CustomerID int OUTPUT
)
AS

INSERT INTO StoreNuke_Customers
(
    FirstName,
    LastName,
    EmailAddress,
    Password,
    IsAdmin
)

VALUES
(
    @FirstName,
    @LastName,
    @Email,
    @Password,
    @IsAdmin
)

SELECT
    @CustomerID = @@Identity

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE StoreNuke_GetAllCustomers
AS
SELECT
	CustomerID,
	FirstName + ' ' + LastName AS FullName,
	FirstName,
	LastName,
	EmailAddress,
	Password,
	IsAdmin
FROM 
	StoreNuke_Customers

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_CustomerAlsoBought
(
    @ProductID int
)
As

/* We want to take the top 5 products contained in
    the orders where someone has purchased the given Product */
SELECT  TOP 5 
    StoreNuke_OrderDetails.ProductID,
    StoreNuke_Products.ModelName,
    SUM(StoreNuke_OrderDetails.Quantity) as TotalNum

FROM    
    StoreNuke_OrderDetails
  INNER JOIN StoreNuke_Products ON StoreNuke_OrderDetails.ProductID = StoreNuke_Products.ProductID

WHERE   OrderID IN 
(
    /* This inner query should retrieve all orders that have contained the productID */
    SELECT DISTINCT OrderID 
    FROM StoreNuke_OrderDetails
    WHERE ProductID = @ProductID
)
AND StoreNuke_OrderDetails.ProductID != @ProductID 
AND StoreNuke_Products.Archived = 0

GROUP BY StoreNuke_OrderDetails.ProductID, StoreNuke_Products.ModelName 

ORDER BY TotalNum DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure StoreNuke_CustomerDetail
(
    @CustomerID int,
    @FirstName   nvarchar(50) OUTPUT,
    @LastName   nvarchar(50) OUTPUT,
    @Email      nvarchar(50) OUTPUT,
    @Password   nvarchar(50) OUTPUT,
    @IsAdmin  Bit OUT
)
AS

SELECT 
    @FirstName = FirstName, 
    @LastName = LastName, 
    @Email    = EmailAddress, 
    @Password = Password,
    @IsAdmin = IsAdmin

FROM 
    StoreNuke_Customers

WHERE 
    CustomerID = @CustomerID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_CustomerLogin
(
    @Email      nvarchar(50),
    @Password   nvarchar(50),
    @CustomerID int OUTPUT
)
AS

SELECT 
    @CustomerID = CustomerID
    
FROM 
    StoreNuke_Customers
    
WHERE 
    EmailAddress = @Email
  AND 
    Password = @Password

IF @@Rowcount < 1 
SELECT 
    @CustomerID = 0

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartEmpty
(
    @CartID nvarchar(50)
)
AS

DELETE FROM StoreNuke_ShoppingCart

WHERE 
    CartID = @CartID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_OrdersAdd
(
    @CustomerID int,
    @CartID     nvarchar(50),
    @OrderDate  datetime,        
    @ShipDate   datetime,
    @AddressID int,
    @OrderID    int OUTPUT
)
AS

BEGIN TRAN AddOrder

/* Create the Order header */
INSERT INTO StoreNuke_Orders
(
    CustomerID, 
    OrderDate, 
    ShipDate,
    AddressID
)
VALUES
(   
    @CustomerID, 
    @OrderDate, 
    @ShipDate,
    @AddressID
)

SELECT
    @OrderID = @@Identity    

/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO StoreNuke_OrderDetails
(
    OrderID, 
    ProductID, 
    Quantity, 
    UnitCost
)

SELECT 
    @OrderID, 
    StoreNuke_ShoppingCart.ProductID, 
    Quantity, 
    StoreNuke_Products.UnitCost

FROM 
    StoreNuke_ShoppingCart 
  INNER JOIN StoreNuke_Products ON StoreNuke_ShoppingCart.ProductID = StoreNuke_Products.ProductID
  
WHERE 
    CartID = @CartID

/* Removal of  items from user's shopping cart will happen on the business layer*/
EXEC StoreNuke_ShoppingCartEmpty @CartID

COMMIT TRAN AddOrder

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_OrdersDetail
(
    @OrderID    int,
    @CustomerID int,
    @OrderDate  datetime OUTPUT,
    @ShipDate   datetime OUTPUT,
    @AddressID   int OUTPUT,
    @OrderTotal money OUTPUT
)
AS

/* Return the order dates from the Orders
    Also verifies the order exists for this customer. */
SELECT 
    @OrderDate = OrderDate,
    @ShipDate = ShipDate,
    @AddressID = AddressID
    
FROM    
    StoreNuke_Orders
    
WHERE   
    OrderID = @OrderID
    AND
    CustomerID = @CustomerID

IF @@Rowcount = 1
BEGIN

/* First, return the OrderTotal out param */
SELECT  
    @OrderTotal = Cast(SUM(StoreNuke_OrderDetails.Quantity * StoreNuke_OrderDetails.UnitCost) as money)
    
FROM    
    StoreNuke_OrderDetails
    
WHERE   
    OrderID= @OrderID

/* Then, return the recordset of info */
SELECT  
    StoreNuke_Products.ProductID, 
    StoreNuke_Products.ModelName,
    StoreNuke_Products.ModelNumber,
    StoreNuke_OrderDetails.UnitCost,
    StoreNuke_OrderDetails.Quantity,
    (StoreNuke_OrderDetails.Quantity * StoreNuke_OrderDetails.UnitCost) as ExtendedAmount

FROM
    StoreNuke_OrderDetails
  INNER JOIN StoreNuke_Products ON StoreNuke_OrderDetails.ProductID = StoreNuke_Products.ProductID
  
WHERE   
    OrderID = @OrderID

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_OrdersList
(
    @CustomerID int
)
As

SELECT  
    StoreNuke_Orders.OrderID,
    Cast(sum(StoreNuke_OrderDetails.Quantity*StoreNuke_OrderDetails.UnitCost) as money) as OrderTotal,
    StoreNuke_Orders.OrderDate, 
    StoreNuke_Orders.ShipDate,
    StoreNuke_Orders.AddressID

FROM    
    StoreNuke_Orders 
  INNER JOIN StoreNuke_OrderDetails ON StoreNuke_Orders.OrderID = StoreNuke_OrderDetails.OrderID

GROUP BY    
    CustomerID, 
    StoreNuke_Orders.OrderID, 
    StoreNuke_Orders.OrderDate, 
    StoreNuke_Orders.ShipDate,
    StoreNuke_Orders.AddressID
HAVING  
    StoreNuke_Orders.CustomerID = @CustomerID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ProductCategoryList
(
	@Archived bit
)
AS

SELECT 
    CategoryID,
    CategoryName,
    CategoryDescription,
    Archived

FROM 
    StoreNuke_Categories
    
WHERE
	Archived = @Archived

ORDER BY 
    CategoryName ASC


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ProductCategoryListAll

AS

SELECT 
    CategoryID,
    CategoryName,
    CategoryDescription,
    Archived

FROM 
    StoreNuke_Categories

ORDER BY 
    CategoryName ASC


GO

CREATE PROCEDURE StoreNuke_DeleteCategory
(
	@CategoryID int,
	@Message int out
)
AS
declare @iCount int

select @iCount  = Count(ProductID) from StoreNuke_Products where CategoryID = @CategoryID

select @Message  =  @iCount 

If @iCount  = 0

DELETE FROM StoreNuke_Categories
WHERE
	CategoryID = @CategoryID

GO

CREATE PROCEDURE StoreNuke_UpdateCategory
(
	@CategoryID int,
	@CategoryName nvarchar (50),
	@CategoryDescription nvarchar (500),
	@Archived bit
)
AS
UPDATE StoreNuke_Categories
SET
	CategoryName = @CategoryName,
	CategoryDescription = @CategoryDescription,
	Archived = @Archived
WHERE
	CategoryID = @CategoryID
GO

CREATE PROCEDURE StoreNuke_AddCategory
(
	@CategoryName nvarchar (50),
	@CategoryDescription nvarchar (500),
	@Archived bit
)
AS
INSERT INTO StoreNuke_Categories
(CategoryName, CategoryDescription, Archived)
VALUES
(@CategoryName, @CategoryDescription, @Archived)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ProductDetail
(
    @ProductID    int
)
AS

SELECT 
    ProductID, ModelNumber, ModelName, ProductImage, UnitCost, Description, CategoryID, Archived

FROM 
    StoreNuke_Products

WHERE 
    ProductID = @ProductID
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ProductsByCategory
(
    @CategoryID int,
    @Archived bit
)
AS

SELECT 
    ProductID,
    ModelName,
    ModelNumber,
    UnitCost, 
    ProductImage,
    Description

FROM 
    StoreNuke_Products

WHERE 
    CategoryID = @CategoryID AND
    Archived = @Archived

ORDER BY 
    ModelName, 
    ModelNumber

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ProductsByCategoryAll
(
    @CategoryID int
)
AS

SELECT 
    ProductID,
    ModelName,
    ModelNumber,
    UnitCost, 
    ProductImage,
    Description

FROM 
    StoreNuke_Products

WHERE 
    CategoryID = @CategoryID

ORDER BY 
    ModelName, 
    ModelNumber

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ProductsMostPopular

AS

SELECT TOP 5 
    StoreNuke_OrderDetails.ProductID, 
    SUM(StoreNuke_OrderDetails.Quantity) as TotalNum, 
    StoreNuke_Products.ModelName
    
FROM    
    StoreNuke_OrderDetails
  INNER JOIN StoreNuke_Products ON StoreNuke_OrderDetails.ProductID = StoreNuke_Products.ProductID

WHERE 
	StoreNuke_Products.Archived = 0
  
GROUP BY 
    StoreNuke_OrderDetails.ProductID, 
    StoreNuke_Products.ModelName
    
ORDER BY 
    TotalNum DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ProductSearch
(
    @Search nvarchar(255)
)
AS

SELECT 
    ProductID,
    ModelName,
    ModelNumber,
    UnitCost, 
    ProductImage

FROM 
    StoreNuke_Products INNER JOIN StoreNuke_Categories ON
    StoreNuke_Products.CategoryID = StoreNuke_Categories.CategoryID

WHERE 
    (ModelNumber LIKE '%' + @Search + '%' 
   OR
    ModelName LIKE '%' + @Search + '%'
   OR
    Description LIKE '%' + @Search + '%') AND
    StoreNuke_Products.Archived = 0 AND
    StoreNuke_Categories.Archived = 0

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ReviewsAdd
(
    @ProductID     int,
    @CustomerName  nvarchar(50),
    @CustomerEmail nvarchar(50),
    @Rating        int,
    @Comments      nvarchar(3850),
    @Authorized    bit,
    @ReviewID      int OUTPUT
)
AS

INSERT INTO StoreNuke_Reviews
(
    ProductID, 
    CustomerName, 
    CustomerEmail, 
    Rating, 
    Comments,
    Authorized
)
VALUES
(
    @ProductID, 
    @CustomerName, 
    @CustomerEmail, 
    @Rating, 
    @Comments,
    @Authorized
)

SELECT 
    @ReviewID = @@Identity

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ReviewsList
(
    @ProductID int
)
AS

SELECT 
    ReviewID, 
    CustomerName, 
    Rating, 
    Comments
    
FROM 
    StoreNuke_Reviews
    
WHERE 
    ProductID = @ProductID AND
    Authorized = 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_GetAllReviewsList
(
    @Authorized int
)
AS

SELECT 
    ReviewID, 
    CustomerName, 
    Rating, 
    Comments
    
FROM 
    StoreNuke_Reviews
    
WHERE 
    Authorized = @Authorized

GO

Create Procedure StoreNuke_UpdateReview
(
    @ReviewID      int,
    @Comments      nvarchar(3850),
    @Authorized    bit
)
AS
UPDATE StoreNuke_Reviews
SET
    Comments = @Comments,
    Authorized = @Authorized

WHERE
    ReviewID = @ReviewID

GO


Create Procedure StoreNuke_DeleteReview
(
    @ReviewID int
)
AS
DELETE    
FROM 
    StoreNuke_Reviews
    
WHERE 
    ReviewID = @ReviewID

GO

CREATE Procedure StoreNuke_GetSingleReview
(
    @ReviewID int
)
AS

SELECT 
    Comments,
    Authorized
    
FROM 
    StoreNuke_Reviews
    
WHERE 
    ReviewID = @ReviewID

GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartAddItem
(
    @CartID nvarchar(50),
    @ProductID int,
    @Quantity int
)
As

DECLARE @CountItems int

SELECT
    @CountItems = Count(ProductID)
FROM
    StoreNuke_ShoppingCart
WHERE
    ProductID = @ProductID
  AND
    CartID = @CartID

IF @CountItems > 0  /* There are items - update the current quantity */

    UPDATE
        StoreNuke_ShoppingCart
    SET
        Quantity = (@Quantity + StoreNuke_ShoppingCart.Quantity)
    WHERE
        ProductID = @ProductID
      AND
        CartID = @CartID

ELSE  /* New entry for this Cart.  Add a new record */

    INSERT INTO StoreNuke_ShoppingCart
    (
        CartID,
        Quantity,
        ProductID
    )
    VALUES
    (
        @CartID,
        @Quantity,
        @ProductID
    )

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartItemCount
(
    @CartID    nvarchar(50),
    @ItemCount int OUTPUT
)
AS

SELECT 
    @ItemCount = COUNT(ProductID)
    
FROM 
    StoreNuke_ShoppingCart
    
WHERE 
    CartID = @CartID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartList
(
    @CartID nvarchar(50)
)
AS

SELECT 
    StoreNuke_Products.ProductID, 
    StoreNuke_Products.ModelName,
    StoreNuke_Products.ModelNumber,
    StoreNuke_ShoppingCart.Quantity,
    StoreNuke_Products.UnitCost,
    Cast((StoreNuke_Products.UnitCost * StoreNuke_ShoppingCart.Quantity) as money) as ExtendedAmount

FROM 
    StoreNuke_Products,
    StoreNuke_ShoppingCart

WHERE 
    StoreNuke_Products.ProductID = StoreNuke_ShoppingCart.ProductID
  AND 
    StoreNuke_ShoppingCart.CartID = @CartID

ORDER BY 
    StoreNuke_Products.ModelName, 
    StoreNuke_Products.ModelNumber

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartMigrate
(
    @OriginalCartId nvarchar(50),
    @NewCartId      nvarchar(50)
)
AS

UPDATE 
    StoreNuke_ShoppingCart
    
SET 
    CartID = @NewCartId 
    
WHERE 
    CartID = @OriginalCartId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartRemoveAbandoned

AS

DELETE FROM StoreNuke_ShoppingCart

WHERE 
    DATEDIFF(dd, DateCreated, GetDate()) > 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartRemoveItem
(
    @CartID nvarchar(50),
    @ProductID int
)
AS

DELETE FROM StoreNuke_ShoppingCart

WHERE 
    CartID = @CartID
  AND
    ProductID = @ProductID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartTotal
(
    @CartID    nvarchar(50),
    @TotalCost money OUTPUT
)
AS

SELECT 
    @TotalCost = SUM(StoreNuke_Products.UnitCost * StoreNuke_ShoppingCart.Quantity)

FROM 
    StoreNuke_ShoppingCart,
    StoreNuke_Products

WHERE
    StoreNuke_ShoppingCart.CartID = @CartID
  AND
    StoreNuke_Products.ProductID = StoreNuke_ShoppingCart.ProductID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure StoreNuke_ShoppingCartUpdate
(
    @CartID    nvarchar(50),
    @ProductID int,
    @Quantity  int
)
AS

UPDATE StoreNuke_ShoppingCart

SET 
    Quantity = @Quantity

WHERE 
    CartID = @CartID 
  AND 
    ProductID = @ProductID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC StoreNuke_AddCustomerAddress
(
	@CustomerID int ,
	@ATTN nvarchar (50) ,
	@Address1 nvarchar (50) ,
	@Address2 nvarchar (50) ,
	@City nvarchar (20) ,
	@RegionCode char (2) ,
	@CountryCode char (2) ,
	@PostalCode nvarchar (10) ,
	@Phone1 nvarchar (20) ,
	@Phone2 nvarchar (20) ,
	@PrimaryAddress bit 
)
AS
INSERT INTO StoreNuke_Addresses
(
	CustomerID,
	ATTN,
	Address1,
	Address2,
	City,
	RegionCode,
	CountryCode,
	PostalCode,
	Phone1,
	Phone2,
	PrimaryAddress
)
VALUES
(
	@CustomerID,
	@ATTN,
	@Address1,
	@Address2,
	@City,
	@RegionCode,
	@CountryCode,
	@PostalCode,
	@Phone1,
	@Phone2,
	@PrimaryAddress
)
GO

CREATE PROC StoreNuke_GetAllAddresses
(
	@CustomerID int
)
AS
SELECT
	AddressID,
	CustomerID,
	ATTN,
	Address1,
	Address2,
	City,
	RegionCode,
	CountryCode,
	PostalCode,
	Phone1,
	Phone2,
	PrimaryAddress
FROM
	StoreNuke_Addresses
WHERE
	@CustomerID = CustomerID
ORDER BY
	PrimaryAddress DESC
GO

CREATE PROC StoreNuke_GetSingleAddresses
(
	@AddressID int
)
AS
SELECT
	AddressID,
	CustomerID,
	ATTN,
	Address1,
	Address2,
	City,
	RegionCode,
	CountryCode,
	PostalCode,
	Phone1,
	Phone2,
	PrimaryAddress
FROM
	StoreNuke_Addresses
WHERE
	@AddressID = AddressID
GO

CREATE PROC StoreNuke_GetPrimaryAddress
(
	@CustomerID int
)
AS
SELECT
	AddressID,
	CustomerID,
	ATTN,
	Address1,
	Address2,
	City,
	RegionCode,
	CountryCode,
	PostalCode,
	Phone1,
	Phone2,
	PrimaryAddress
FROM
	StoreNuke_Addresses
WHERE
	@CustomerID = CustomerID AND
	PrimaryAddress = 1
GO

CREATE PROC StoreNuke_GetAddressByAddressID
(
	@AddressID int
)
AS
SELECT
	AddressID,
	CustomerID,
	ATTN,
	Address1,
	Address2,
	City,
	RegionCode,
	CountryCode,
	PostalCode,
	Phone1,
	Phone2,
	PrimaryAddress
FROM
	StoreNuke_Addresses
WHERE
	AddressID = @AddressID
GO

CREATE PROC StoreNuke_UpdateCustomerAddress
(
	@AddressID int ,
	@ATTN nvarchar (50) ,
	@Address1 nvarchar (50) ,
	@Address2 nvarchar (50) ,
	@City nvarchar (20) ,
	@RegionCode char (2) ,
	@CountryCode char (2) ,
	@PostalCode nvarchar (10) ,
	@Phone1 nvarchar (20) ,
	@Phone2 nvarchar (20) ,
	@PrimaryAddress bit 
)
AS
UPDATE StoreNuke_Addresses
SET
	ATTN = @ATTN,
	Address1 = @Address1,
	Address2 = @Address2,
	City = @City,
	RegionCode = @RegionCode,
	CountryCode = @CountryCode,
	PostalCode = @PostalCode,
	Phone1 = @Phone1,
	Phone2 = @Phone2,
	PrimaryAddress = @PrimaryAddress
WHERE
	AddressID = @AddressID
GO

CREATE PROC StoreNuke_GetCountryCode
AS
SELECT
	Code, Description
FROM
	StoreNuke_CodeCountry
ORDER BY
	Description
GO

CREATE PROC StoreNuke_GetRegionCode
(
	@Country char(2)
)
AS
SELECT
	Code, Description
FROM
	StoreNuke_CodeRegion
WHERE
	Country = @Country
ORDER BY
	Description
GO

--
-- end make objects
-- 

-- ===============================================
-- CONFIG ASP.NET StoreNuke STARTER KIT DATABASE
-- Load Sample Data Script
-- 
-- Version:	1.2 - 01/02 (swarren)
--
-- ===============================================

-- =======================================================
-- INSERT INITIAL DATA INTO IBUYSPY StoreNuke DB
-- =======================================================

-- point to proper DB 
-- use [StoreNuke]
-- GO

SET DATEFORMAT mdy
GO

DELETE FROM [dbo].[StoreNuke_Categories]

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Categories] ON

GO

-- Country Codes

insert StoreNuke_CodeCountry ( Code,Description )  select 'AD','Andorra'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AE','United Arab Emirates'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AF','Afghanistan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AG','Antigua and Barbuda'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AI','Anguilla'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AL','Albania'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AM','Armenia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AN','Netherlands Antilles'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AO','Angola'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AQ','Antarctica'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AR','Argentina'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AS','American Samoa'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AT','Austria'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AU','Australia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AW','Aruba'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'AZ','Azerbaijan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BA','Bosnia and Herzegovina'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BB','Barbados'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BD','Bangladesh'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BE','Belgium'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BF','Burkina Faso'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BG','Bulgaria'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BH','Bahrain'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BI','Burundi'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BJ','Benin'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BM','Bermuda'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BN','Brunei Darussalam'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BO','Bolivia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BR','Brazil'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BS','Bahamas'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BT','Bhutan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BV','Bouvet Island'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BW','Botswana'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BY','Belarus'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'BZ','Belize'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CA','Canada'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CC','Cocos'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CF','Central African Republic'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CG','Congo'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CH','Switzerland'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CI','Ivory Coast'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CK','Cook Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CL','Chile'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CM','Cameroon'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CN','China'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CO','Colombia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CR','Costa Rica'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CU','Cuba'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CV','Cape Verde'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CX','Christmas Island'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CY','Cyprus'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'CZ','Czech Republic'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'DE','Germany'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'DJ','Djibouti'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'DK','Denmark'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'DM','Dominica'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'DO','Dominican Republic'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'DZ','Algeria'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'EC','Ecuador'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'EE','Estonia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'EG','Egypt'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'EH','Western Sahara'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ER','Eritrea'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ES','Spain'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ET','Ethiopia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'FI','Finland'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'FJ','Fiji'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'FK','Falkland Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'FM','Micronesia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'FO','Faroe Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'FR','France'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GA','Gabon'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GD','Grenada'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GE','Georgia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GF','French Guiana'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GH','Ghana'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GI','Gibraltar'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GL','Greenland'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GM','Gambia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GN','Guinea'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GP','Guadeloupe'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GQ','Equatorial Guinea'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GR','Greece'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GS','S. Georgia and S. Sandwich Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GT','Guatemala'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GU','Guam'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GW','Guinea-Bissau'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'GY','Guyana'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'HK','Hong Kong'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'HM','Heard and McDonald Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'HN','Honduras'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'HR','Croatia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'HT','Haiti'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'HU','Hungary'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ID','Indonesia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IE','Ireland'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IL','Israel'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IN','India'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IO','British Indian Ocean Territory'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IQ','Iraq'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IR','Iran'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IS','Iceland'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'IT','Italy'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'JM','Jamaica'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'JO','Jordan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'JP','Japan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KE','Kenya'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KG','Kyrgyzstan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KH','Cambodia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KI','Kiribati'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KM','Comoros'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KN','Saint Kitts and Nevis'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KP','North Korea'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KR','South Korea'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KW','Kuwait'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KY','Cayman Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'KZ','Kazakhstan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LA','Laos'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LB','Lebanon'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LC','Saint Lucia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LI','Liechtenstein'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LK','Sri Lanka'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LR','Liberia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LS','Lesotho'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LT','Lithuania'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LU','Luxembourg'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LV','Latvia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'LY','Libya'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MA','Morocco'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MC','Monaco'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MD','Moldova'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MG','Madagascar'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MH','Marshall Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MK','Macedonia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ML','Mali'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MM','Myanmar'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MN','Mongolia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MO','Macau'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MP','Northern Mariana Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MQ','Martinique'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MR','Mauritania'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MS','Montserrat'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MT','Malta'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MU','Mauritius'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MV','Maldives'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MW','Malawi'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MX','Mexico'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MY','Malaysia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'MZ','Mozambique'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NA','Namibia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NC','New Caledonia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NE','Niger'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NF','Norfolk Island'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NG','Nigeria'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NI','Nicaragua'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NL','Netherlands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NO','Norway'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NP','Nepal'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NR','Nauru'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NU','Niue'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'NZ','New Zealand'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'OM','Oman'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PA','Panama'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PE','Peru'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PF','French Polynesia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PG','Papua New Guinea'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PH','Philippines'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PK','Pakistan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PL','Poland'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PM','St. Pierre and Miquelon'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PN','Pitcairn'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PR','Puerto Rico'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PT','Portugal'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PW','Palau'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'PY','Paraguay'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'QA','Qatar'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'RE','Reunion'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'RO','Romania'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'RU','Russian Federation'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'RW','Rwanda'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SA','Saudi Arabia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SB','Solomon Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SC','Seychelles'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SD','Sudan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SE','Sweden'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SG','Singapore'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SH','St. Helena'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SI','Slovenia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SJ','Svalbard and Jan Mayen Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SK','Slovakia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SL','Sierra Leone'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SM','San Marino'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SN','Senegal'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SO','Somalia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SR','Suriname'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ST','Sao Tome and Principe'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SU','Soviet Union'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SV','El Salvador'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SY','Syria'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'SZ','Swaziland'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TC','Turks and Caicos Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TD','Chad'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TF','French Southern Territories'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TG','Togo'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TH','Thailand'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TJ','Tajikistan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TK','Tokelau'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TM','Turkmenistan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TN','Tunisia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TO','Tonga'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TP','East Timor'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TR','Turkey'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TT','Trinidad and Tobago'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TV','Tuvalu'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TW','Taiwan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'TZ','Tanzania'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'UA','Ukraine'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'UG','Uganda'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'UK','United Kingdom'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'UM','US Minor Outlying Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'US','United States'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'UY','Uruguay'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'UZ','Uzbekistan'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'VC','Saint Vincent and The Grenadines'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'VE','Venezuela'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'VG','British Virgin Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'VI','US Virgin Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'VN','Viet Nam'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'VU','Vanuatu'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'WF','Wallis and Futuna Islands'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'WS','Samoa'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'YE','Yemen'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'YT','Mayotte'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'YU','Yugoslavia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ZA','South Africa'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ZM','Zambia'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ZR','Zaire'
GO
insert StoreNuke_CodeCountry ( Code,Description )  select 'ZW','Zimbabwe'
GO

-- Region Code

insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'AB','Alberta','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'AK','Alaska','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'AL','Alabama','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'AR','Arkansas','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'AZ','Arizona','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'BC','British Columbia','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'CA','California','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'CO','Colorado','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'CT','Connecticut','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'DC','District of Columbia','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'DE','Delaware','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'FL','Florida','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'GA','Georgia','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'HI','Hawaii','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'IA','Iowa','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'ID','Idaho','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'IL','Illinois','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'IN','Indiana','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'KS','Kansas','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'KY','Kentucky','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'LA','Louisiana','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MA','Massachusetts','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MB','Manitoba','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MD','Maryland','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'ME','Maine','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MI','Michigan','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MN','Minnesota','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MO','Missouri','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MS','Mississippi','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'MT','Montana','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NB','New Brunswick','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NC','North Carolina','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'ND','North Dakota','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NE','Nebraska','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NF','Newfoundland','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NH','New Hampshire','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NJ','New Jersey','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NM','New Mexico','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NS','Nova Scotia','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NT','Northwest Territories','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NV','Nunavut','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NV','Nevada','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'NY','New York','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'OH','Ohio','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'OK','Oklahoma','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'ON','Ontario','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'OR','Oregon','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'PA','Pennsylvania','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'PE','Prince Edward Island','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'QC','Quebec','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'RI','Rhode Island','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'SC','South Carolina','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'SD','South Dakota','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'SK','Saskatchewan','CA'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'TN','Tennessee','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'TX','Texas','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'UT','Utah','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'VA','Virginia','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'VT','Vermont','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'WA','Washington','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'WI','Wisconsin','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'WV','West Virginia','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'WY','Wyoming','US'
GO
insert StoreNuke_CodeRegion ( Code,Description,Country )  select 'YT','Yukon Territory','CA'
GO

INSERT INTO [dbo].[StoreNuke_Categories]
([CategoryID],[CategoryName], [CategoryDescription])
SELECT 14, 'Communications', 'Communication Devices'
UNION ALL
SELECT 15, 'Deception', 'Tools To Spy With'
UNION ALL
SELECT 16, 'Travel', 'Travel Items'
UNION ALL
SELECT 17, 'Protection', 'Protective Items'
UNION ALL
SELECT 18, 'Munitions', 'Tools To Protect and Defend'
UNION ALL
SELECT 19, 'Tools', 'Misc Tools'
UNION ALL
SELECT 20, 'General', 'General Items'

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Categories] OFF

GO

-- Vertigo Software Data Script File
-- This file was auto-generated by Utilities.DataScripter Version 1.0.1152.18116 on 2/26/2003 at 1:02 PM.

DELETE FROM [dbo].[StoreNuke_Customers]

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Customers] ON

GO

INSERT INTO [dbo].[StoreNuke_Customers]
([CustomerID],[FirstName], [LastName],[EmailAddress],[Password], [IsAdmin])
SELECT 1, 'Big', 'Guy', 'bg@aj.net', '123',0
UNION ALL
SELECT 2, 'Sarah', 'Goodpenny', 'sg@ibuyspy.com', '15-72-5B-0E-E7-A6-5E-A3-4A-BF-84-C8-58-93-18-FA',0
UNION ALL
SELECT 3, 'Gordon', 'Que', 'gq@ibuyspy.com', '15-72-5B-0E-E7-A6-5E-A3-4A-BF-84-C8-58-93-18-FA',0
UNION ALL
SELECT 20, 'hash', 'test', 'hash@hash.com', '5F-A2-85-E1-BE-BE-0A-66-23-E3-3A-FC-04-A1-FB-D5',0
UNION ALL
SELECT 19, 'Guest', 'Account', 'guest@asp.net', 'D0-09-1A-0F-E2-B2-09-34-D8-8B-46-06-84-F5-97-89',1
UNION ALL
SELECT 16, 'Test', 'Account', 'd', '2B-9D-4F-A8-5C-8E-82-13-2B-DE-46-B1-43-04-01-42',0

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Customers] OFF

GO

INSERT INTO [StoreNuke_Addresses]
([CustomerID], [ATTN], [Address1], [Address2], [City], [RegionCode], [CountryCode], [PostalCode], [Phone1], [Phone2], [PrimaryAddress])
SELECT 1, 'Big Guy', '123 N 5th St', '', 'Boise', 'ID', 'US', '83713', '208-555-1212', '', 1
UNION ALL
SELECT 2, 'Sarah Goodpenny', '1123 W Wells St', '', 'Boise', 'ID', 'US', '83703', '208-555-1212', '', 1
UNION ALL
SELECT 3, 'Gordon Que', '12 E Fairview', '', 'Meridian', 'ID', 'US', '83743', '208-555-1212', '', 1
UNION ALL
SELECT 20, 'hash test', '9821 W Ustick', '', 'Boise', 'ID', 'US', '83702', '208-555-1212', '', 1
UNION ALL
SELECT 19, 'Guest Account', '1110 N Five Mile Rd', '', 'Boise', 'ID', 'US', '83713', '208-353-6211', '', 1
UNION ALL
SELECT 16, 'Test Account', '1231 Omphale St', '', 'Kuna', 'ID', 'US', '83634', '208-555-1212', '', 1

go

-- Vertigo Software Data Script File
-- This file was auto-generated by Utilities.DataScripter Version 1.0.1152.18116 on 2/26/2003 at 1:02 PM.

DELETE FROM [dbo].[StoreNuke_Orders]

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Orders] ON

GO

INSERT INTO [dbo].[StoreNuke_Orders]
([OrderID],[CustomerID],[OrderDate],[ShipDate], [AddressID])
SELECT 99, 19, '7/6/2000 1:01:00 AM', '7/7/2000 1:01:00 AM', 5
UNION ALL
SELECT 93, 16, '7/3/2000 1:01:00 AM', '7/4/2000 1:01:00 AM', 6
UNION ALL
SELECT 101, 16, '7/10/2000 1:01:00 AM', '7/11/2000 1:01:00 AM', 6
UNION ALL
SELECT 103, 16, '7/10/2000 1:01:00 AM', '7/10/2000 1:01:00 AM', 6
UNION ALL
SELECT 96, 19, '7/3/2000 1:01:00 AM', '7/3/2000 1:01:00 AM', 5
UNION ALL
SELECT 104, 19, '7/10/2000 1:01:00 AM', '7/11/2000 1:01:00 AM', 5
UNION ALL
SELECT 105, 16, '10/30/2000 1:01:00 AM', '10/31/2000 1:01:00 AM', 6
UNION ALL
SELECT 106, 16, '10/30/2000 1:01:00 AM', '10/30/2000 1:01:00 AM', 6
UNION ALL
SELECT 107, 16, '10/30/2000 1:01:00 AM', '10/31/2000 1:01:00 AM', 6
UNION ALL
SELECT 100, 19, '7/6/2000 1:01:00 AM', '7/8/2000 1:01:00 AM', 5
UNION ALL
SELECT 102, 16, '7/10/2000 1:01:00 AM', '7/12/2000 1:01:00 AM', 6
UNION ALL
SELECT 108, 1, '2/24/2003 11:32:07 AM', '2/24/2003 11:32:07 AM', 1

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Orders] OFF

GO

-- Vertigo Software Data Script File
-- This file was auto-generated by Utilities.DataScripter Version 1.0.1152.18116 on 2/26/2003 at 1:02 PM.

DELETE FROM [dbo].[StoreNuke_OrderDetails]

GO

INSERT INTO [dbo].[StoreNuke_OrderDetails]
([OrderID],[ProductID],[Quantity],[UnitCost])
SELECT 99, 404, 2, 459.99
UNION ALL
SELECT 93, 363, 1, 1.99
UNION ALL
SELECT 101, 378, 2, 14.99
UNION ALL
SELECT 102, 372, 1, 129.99
UNION ALL
SELECT 96, 378, 1, 14.99
UNION ALL
SELECT 103, 363, 1, 1.99
UNION ALL
SELECT 104, 355, 1, 1499.99
UNION ALL
SELECT 104, 378, 1, 14.99
UNION ALL
SELECT 104, 406, 1, 399.99
UNION ALL
SELECT 100, 404, 2, 459.99
UNION ALL
SELECT 101, 401, 1, 599.99
UNION ALL
SELECT 102, 401, 1, 599.99
UNION ALL
SELECT 104, 362, 1, 1.99
UNION ALL
SELECT 104, 404, 1, 459.99
UNION ALL
SELECT 105, 355, 2, 1499.99
UNION ALL
SELECT 106, 401, 1, 599.99
UNION ALL
SELECT 106, 404, 2, 459.99
UNION ALL
SELECT 107, 368, 2, 19999.98
UNION ALL
SELECT 108, 378, 1, 14.99

GO

-- Vertigo Software Data Script File
-- This file was auto-generated by Utilities.DataScripter Version 1.0.1152.18116 on 2/26/2003 at 1:02 PM.

DELETE FROM [dbo].[StoreNuke_Products]

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Products] ON

GO

INSERT INTO [dbo].[StoreNuke_Products]
([ProductID],[CategoryID],[ModelNumber],[ModelName],[ProductImage],[UnitCost],[Description])
SELECT 355, 16, 'RU007', 'Rain Racer 2000', 'image.gif', 1499.99, 'Looks like an ordinary bumbershoot, but don''t be fooled! Simply place Rain Racer''s tip on the ground and press the release latch. Within seconds, this ordinary rain umbrella converts into a two-wheeled gas-powered mini-scooter. Goes from 0 to 60 in 7.5 seconds - even in a driving rain! Comes in black, blue, and candy-apple red.'
UNION ALL
SELECT 356, 20, 'STKY1', 'Edible Tape', 'image.gif', 3.99, 'The latest in personal survival gear, the STKY1 looks like a roll of ordinary office tape, but can save your life in an emergency.  Just remove the tape roll and place in a kettle of boiling water with mixed vegetables and a ham shank. In just 90 minutes you have a great tasking soup that really sticks to your ribs! Herbs and spices not included.'
UNION ALL
SELECT 357, 16, 'P38', 'Escape Vehicle (Air)', 'image.gif', 2.99, 'In a jam, need a quick escape? Just whip out a sheet of our patented P38 paper and, with a few quick folds, it converts into a lighter-than-air escape vehicle! Especially effective on windy days - no fuel required. Comes in several sizes including letter, legal, A10, and B52.'
UNION ALL
SELECT 358, 19, 'NOZ119', 'Extracting Tool', 'image.gif', 199, 'High-tech miniaturized extracting tool. Excellent for extricating foreign objects from your person. Good for picking up really tiny stuff, too! Cleverly disguised as a pair of tweezers. '
UNION ALL
SELECT 359, 16, 'PT109', 'Escape Vehicle (Water)', 'image.gif', 1299.99, 'Camouflaged as stylish wing tips, these ''shoes'' get you out of a jam on the high seas instantly. Exposed to water, the pair transforms into speedy miniature inflatable rafts. Complete with 76 HP outboard motor, these hip heels will whisk you to safety even in the roughest of seas. Warning: Not recommended for beachwear.'
UNION ALL
SELECT 360, 14, 'RED1', 'Communications Device', 'image.gif', 0.00, 'Subversively stay in touch with this miniaturized wireless communications device. Speak into the pointy end and listen with the other end! Voice-activated dialing makes calling for backup a breeze. Excellent for undercover work at schools, rest homes, and most corporate headquarters. Comes in assorted colors.'
UNION ALL
SELECT 362, 14, 'LK4TLNT', 'Persuasive Pencil', 'image.gif', 1.99, 'Persuade anyone to see your point of view!  Captivate your friends and enemies alike!  Draw the crime-scene or map out the chain of events.  All you need is several years of training or copious amounts of natural talent. You''re halfway there with the Persuasive Pencil. Purchase this item with the Retro Pocket Protector Rocket Pack for optimum disguise.'
UNION ALL
SELECT 363, 18, 'NTMBS1', 'Multi-Purpose Rubber Band', 'image.gif', 1.99, 'One of our most popular items!  A band of rubber that stretches  20 times the original size. Uses include silent one-to-one communication across a crowded room, holding together a pack of Persuasive Pencils, and powering lightweight aircraft. Beware, stretching past 20 feet results in a painful snap and a rubber strip.'
UNION ALL
SELECT 364, 19, 'NE1RPR', 'Universal Repair System', 'image.gif', 4.99, 'Few people appreciate the awesome repair possibilities contained in a single roll of duct tape. In fact, some houses in the Midwest are made entirely out of the miracle material contained in every roll! Can be safely used to repair cars, computers, people, dams, and a host of other items.'
UNION ALL
SELECT 365, 19, 'BRTLGT1', 'Effective Flashlight', 'image.gif', 9.99, 'The most powerful darkness-removal device offered to creatures of this world.  Rather than amplifying existing/secondary light, this handy product actually REMOVES darkness allowing you to see with your own eyes.  Must-have for nighttime operations. An affordable alternative to the Night Vision Goggles.'
UNION ALL
SELECT 367, 18, 'INCPPRCLP', 'The Incredible Versatile Paperclip', 'image.gif', 1.49, 'This 0. 01 oz piece of metal is the most versatile item in any respectable spy''s toolbox and will come in handy in all sorts of situations. Serves as a wily lock pick, aerodynamic projectile (used in conjunction with Multi-Purpose Rubber Band), or escape-proof finger cuffs.  Best of all, small size and pliability means it fits anywhere undetected.  Order several today!'
UNION ALL
SELECT 368, 16, 'DNTRPR', 'Toaster Boat', 'image.gif', 19999.98, 'Turn breakfast into a high-speed chase! In addition to toasting bagels and breakfast pastries, this inconspicuous toaster turns into a speedboat at the touch of a button. Boasting top speeds of 60 knots and an ultra-quiet motor, this valuable item will get you where you need to be ... fast! Best of all, Toaster Boat is easily repaired using a Versatile Paperclip or a standard butter knife. Manufacturer''s Warning: Do not submerge electrical items.'
UNION ALL
SELECT 370, 17, 'TGFDA', 'Multi-Purpose Towelette', 'image.gif', 12.99, 'Don''t leave home without your monogrammed towelette! Made from lightweight, quick-dry fabric, this piece of equipment has more uses in a spy''s day than a Swiss Army knife. The perfect all-around tool while undercover in the locker room: serves as towel, shield, disguise, sled, defensive weapon, whip and emergency food source. Handy bail gear for the Toaster Boat. Monogram included with purchase price.'
UNION ALL
SELECT 371, 18, 'WOWPEN', 'Mighty Mighty Pen', 'image.gif', 129.99, 'Some spies claim this item is more powerful than a sword. After examining the titanium frame, built-in blowtorch, and Nerf dart-launcher, we tend to agree! '
UNION ALL
SELECT 372, 20, 'ICNCU', 'Perfect-Vision Glasses', 'image.gif', 129.99, 'Avoid painful and potentially devastating laser eye surgery and contact lenses. Cheaper and more effective than a visit to the optometrist, these Perfect-Vision Glasses simply slide over nose and eyes and hook on ears. Suddenly you have 20/20 vision! Glasses also function as HUD (Heads Up Display) for most European sports cars manufactured after 1992.'
UNION ALL
SELECT 373, 17, 'LKARCKT', 'Pocket Protector Rocket Pack', 'image.gif', 1.99, 'Any debonair spy knows that this accoutrement is coming back in style. Flawlessly protects the pockets of your short-sleeved oxford from unsightly ink and pencil marks. But there''s more! Strap it on your back and it doubles as a rocket pack. Provides enough turbo-thrust for a 250-pound spy or a passel of small children. Maximum travel radius: 3000 miles.'
UNION ALL
SELECT 374, 15, 'DNTGCGHT', 'Counterfeit Creation Wallet', 'image.gif', 999.99, 'Don''t be caught penniless in Prague without this hot item! Instantly creates replicas of most common currencies! Simply place rocks and water in the wallet, close, open up again, and remove your legal tender!'
UNION ALL
SELECT 375, 16, 'WRLD00', 'Global Navigational System', 'image.gif', 29.99, 'No spy should be without one of these premium devices. Determine your exact location with a quick flick of the finger. Calculate destination points by spinning, closing your eyes, and stopping it with your index finger.'
UNION ALL
SELECT 376, 15, 'CITSME9', 'Cloaking Device', 'image.gif', 9999.99, 'Worried about detection on your covert mission? Confuse mission-threatening forces with this cloaking device. Powerful new features include string-activated pre-programmed phrases such as "Danger! Danger!", "Reach for the sky!", and other anti-enemy expressions. Hyper-reactive karate chop action deters even the most persistent villain.'
UNION ALL
SELECT 377, 15, 'BME007', 'Identity Confusion Device', 'image.gif', 6.99, 'Never leave on an undercover mission without our Identity Confusion Device! If a threatening person approaches, deploy the device and point toward the oncoming individual. The subject will fail to recognize you and let you pass unnoticed. Also works well on dogs.'
UNION ALL
SELECT 379, 17, 'SHADE01', 'Ultra Violet Attack Defender', 'image.gif', 89.99, 'Be safe and suave. A spy wearing this trendy article of clothing is safe from ultraviolet ray-gun attacks. Worn correctly, the Defender deflects rays from ultraviolet weapons back to the instigator. As a bonus, also offers protection against harmful solar ultraviolet rays, equivalent to SPF 50.'
UNION ALL
SELECT 378, 17, 'SQUKY1', 'Guard Dog Pacifier', 'image.gif', 14.99, 'Pesky guard dogs become a spy''s best friend with the Guard Dog Pacifier. Even the most ferocious dogs suddenly act like cuddly kittens when they see this prop.  Simply hold the device in front of any threatening dogs, shaking it mildly.  For tougher canines, a quick squeeze emits an irresistible squeak that never fails to  place the dog under your control.'
UNION ALL
SELECT 382, 20, 'CHEW99', 'Survival Bar', 'image.gif', 6.99, 'Survive for up to four days in confinement with this handy item. Disguised as a common eraser, it''s really a high-calorie food bar. Stranded in hostile territory without hope of nourishment? Simply break off a small piece of the eraser and chew vigorously for at least twenty minutes. Developed by the same folks who created freeze-dried ice cream, powdered drink mix, and glow-in-the-dark shoelaces.'
UNION ALL
SELECT 402, 20, 'C00LCMB1', 'Telescoping Comb', 'image.gif', 399.99, 'Use the Telescoping Comb to track down anyone, anywhere! Deceptively simple, this is no normal comb. Flip the hidden switch and two telescoping lenses project forward creating a surprisingly powerful set of binoculars (50X). Night-vision add-on is available for midnight hour operations.'
UNION ALL
SELECT 384, 19, 'FF007', 'Eavesdrop Detector', 'image.gif', 99.99, 'Worried that counteragents have placed listening devices in your home or office? No problem! Use our bug-sweeping wiener to check your surroundings for unwanted surveillance devices. Just wave the frankfurter around the room ... when bugs are detected, this "foot-long" beeps! Comes complete with bun, relish, mustard, and headphones for privacy.'
UNION ALL
SELECT 385, 16, 'LNGWADN', 'Escape Cord', 'image.gif', 13.99, 'Any agent assigned to mountain terrain should carry this ordinary-looking extension cord ... except that it''s really a rappelling rope! Pull quickly on each end to convert the electrical cord into a rope capable of safely supporting up to two agents. Comes in various sizes including Mt McKinley, Everest, and Kilimanjaro. WARNING: To prevent serious injury, be sure to disconnect from wall socket before use.'
UNION ALL
SELECT 386, 17, '1MOR4ME', 'Cocktail Party Pal', 'image.gif', 69.99, 'Do your assignments have you flitting from one high society party to the next? Worried about keeping your wits about you as you mingle witih the champagne-and-caviar crowd? No matter how many drinks you''re offered, you can safely operate even the most complicated heavy machinery as long as you use our model 1MOR4ME alcohol-neutralizing coaster. Simply place the beverage glass on the patented circle to eliminate any trace of alcohol in the drink.'
UNION ALL
SELECT 387, 20, 'SQRTME1', 'Remote Foliage Feeder', 'image.gif', 9.99, 'Even spies need to care for their office plants.  With this handy remote watering device, you can water your flowers as a spy should, from the comfort of your chair.  Water your plants from up to 50 feet away.  Comes with an optional aiming system that can be mounted to the top for improved accuracy.'
UNION ALL
SELECT 388, 20, 'ICUCLRLY00', 'Contact Lenses', 'image.GIF', 59.99, 'Traditional binoculars and night goggles can be bulky, especially for assignments in confined areas. The problem is solved with these patent-pending contact lenses, which give excellent visibility up to 100 miles. New feature: now with a night vision feature that permits you to see in complete darkness! Contacts now come in a variety of fashionable colors for coordinating with your favorite ensembles.'
UNION ALL
SELECT 389, 20, 'OPNURMIND', 'Telekinesis Spoon', 'image.gif', 2.99, 'Learn to move things with your mind! Broaden your mental powers using this training device to hone telekinesis skills. Simply look at the device, concentrate, and repeat "There is no spoon" over and over.'
UNION ALL
SELECT 390, 19, 'ULOST007', 'Rubber Stamp Beacon', 'image.gif', 129.99, 'With the Rubber Stamp Beacon, you''ll never get lost on your missions again. As you proceed through complicated terrain, stamp a stationary object with this device. Once an object has been stamped, the stamp''s patented ink will emit a signal that can be detected up to 153.2 miles away by the receiver embedded in the device''s case. WARNING: Do not expose ink to water.'
UNION ALL
SELECT 391, 17, 'BSUR2DUC', 'Bullet Proof Facial Tissue', 'image.gif', 79.99, 'Being a spy can be dangerous work. Our patented Bulletproof Facial Tissue gives a spy confidence that any bullets in the vicinity risk-free. Unlike traditional bulletproof devices, these lightweight tissues have amazingly high tensile strength. To protect the upper body, simply place a tissue in your shirt pocket. To protect the lower body, place a tissue in your pants pocket. If you do not have any pockets, be sure to check out our Bulletproof Tape. 100 tissues per box. WARNING: Bullet must not be moving for device to successfully stop penetration.'
UNION ALL
SELECT 393, 20, 'NOBOOBOO4U', 'Speed Bandages', 'image.GIF', 3.99, 'Even spies make mistakes.  Barbed wire and guard dogs pose a threat of injury for the active spy.  Use our special bandages on cuts and bruises to rapidly heal the injury.  Depending on the severity of the wound, the bandages can take between 10 to 30 minutes to completely heal the injury.'
UNION ALL
SELECT 394, 15, 'BHONST93', 'Correction Fluid', 'image.gif', 1.99, 'Disguised as typewriter correction fluid, this scientific truth serum forces subjects to correct anything not perfectly true. Simply place a drop of the special correction fluid on the tip of the subject''s nose. Within seconds, the suspect will automatically correct every lie. Effects from Correction Fluid last approximately 30 minutes per drop. WARNING: Discontinue use if skin appears irritated.'
UNION ALL
SELECT 396, 19, 'BPRECISE00', 'Dilemma Resolution Device', 'image.gif', 11.99, 'Facing a brick wall? Stopped short at a long, sheer cliff wall?  Carry our handy lightweight calculator for just these emergencies. Quickly enter in your dilemma and the calculator spews out the best solutions to the problem.   Manufacturer Warning: Use at own risk. Suggestions may lead to adverse outcomes.'
UNION ALL
SELECT 397, 14, 'LSRPTR1', 'Nonexplosive Cigar', 'image.gif', 29.99, 'Contrary to popular spy lore, not all cigars owned by spies explode! Best used during mission briefings, our Nonexplosive Cigar is really a cleverly-disguised, top-of-the-line, precision laser pointer. Make your next presentation a hit.'
UNION ALL
SELECT 399, 20, 'QLT2112', 'Document Transportation System', 'image.gif', 299.99, 'Keep your stolen Top Secret documents in a place they''ll never think to look!  This patent leather briefcase has multiple pockets to keep documents organized.  Top quality craftsmanship to last a lifetime.'
UNION ALL
SELECT 400, 15, 'THNKDKE1', 'Hologram Cufflinks', 'image.gif', 799.99, 'Just point, and a turn of the wrist will project a hologram of you up to 100 yards away. Sneaking past guards will be child''s play when you''ve sent them on a wild goose chase. Note: Hologram adds ten pounds to your appearance.'
UNION ALL
SELECT 401, 14, 'TCKLR1', 'Fake Moustache Translator', 'image.gif', 599.99, 'Fake Moustache Translator attaches between nose and mouth to double as a language translator and identity concealer. Sophisticated electronics translate your voice into the desired language. Wriggle your nose to toggle between Spanish, English, French, and Arabic. Excellent on diplomatic missions.'
UNION ALL
SELECT 404, 14, 'JWLTRANS6', 'Interpreter Earrings', 'image.gif', 459.99, 'The simple elegance of our stylish monosex earrings accents any wardrobe, but their clean lines mask the sophisticated technology within. Twist the lower half to engage a translator function that intercepts spoken words in any language and converts them to the wearer''s native tongue. Warning: do not use in conjunction with our Fake Moustache Translator product, as the resulting feedback loop makes any language sound like Pig Latin.'
UNION ALL
SELECT 406, 19, 'GRTWTCH9', 'Multi-Purpose Watch', 'image.gif', 399.99, 'In the tradition of famous spy movies, the Multi Purpose Watch comes with every convenience! Installed with lighter, TV, camera, schedule-organizing software, MP3 player, water purifier, spotlight, and tire pump. Special feature: Displays current date and time. Kitchen sink add-on will be available in the fall of 2001.'

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Products] OFF

GO

-- Vertigo Software Data Script File
-- This file was auto-generated by Utilities.DataScripter Version 1.0.1152.18116 on 2/26/2003 at 1:02 PM.

DELETE FROM [dbo].[StoreNuke_Reviews]

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Reviews] ON

GO

INSERT INTO [dbo].[StoreNuke_Reviews]
([ReviewID],[ProductID],[CustomerName],[CustomerEmail],[Rating],[Comments],[Authorized])
SELECT 21, 404, 'Sarah Goodpenny', 'sg@ibuyspy.com', 5, 'Really smashing! &nbsp;Don''t know how I''d get by without them!', 1
UNION ALL
SELECT 22, 378, 'James Bondwell', 'jb@ibuyspy.com', 3, 'Well made, but only moderately effective. &nbsp;Ouch!', 0

GO

SET IDENTITY_INSERT [dbo].[StoreNuke_Reviews] OFF

GO

DECLARE @fmt nvarchar(3)
SELECT @fmt = dateformat FROM master..syslanguages WHERE name = @@Language
SET DATEFORMAT @fmt
GO
--
-- End load data
-- 

-- =======================================================
-- ADD SCHEDULED JOBS TO IBUYSPY StoreNuke DB
-- =======================================================

Declare @JobCount int
DECLARE @tmp nvarchar(150)

SELECT @tmp = db_name()

USE msdb

SELECT @JobCount=Count(name) FROM sysjobs where name='RemoveAbandonedCarts'

If @JobCount<>0	exec sp_delete_job @job_name='RemoveAbandonedCarts' 

exec sp_add_job
	@job_name = 'RemoveAbandonedCarts',
	@enabled = 1,
	@notify_level_eventlog = 3

exec sp_add_jobserver
	@job_name = 'RemoveAbandonedCarts',
	@server_name = "(local)"

exec sp_add_jobstep
	@job_name = 'RemoveAbandonedCarts',
	@step_name = 'Step 1',
	@command = "exec ShoppingCartRemoveAbandoned",
	@database_name = @tmp,
	@retry_attempts = 2,
	@retry_interval = 5

exec sp_add_jobschedule
	@job_name = 'RemoveAbandonedCarts',
	@name = 'Every day at 12AM',
	@freq_type = 4,
	@freq_interval = 1,
	@freq_subday_type = 1

GO
--
-- end scheduled jobs
-- 
